create external table jms_dm.dm_network_retention_summary_dt (
is_contain_difficult   string COMMENT '是否包含问题件 1 是 2 否' ,
end_manage_region_code  string COMMENT '末端网点所属管理大区编码' ,
end_manage_region_name   string COMMENT '末端网点所属管理大区名称' ,
end_agent_code   string COMMENT '末端网点所属代理区编码' ,
end_agent_name   string COMMENT '末端网点所属代理区名称' ,
end_provider_id   string COMMENT '末端网点所属省份id' ,
end_provider_name   string COMMENT '末端网点所属省份名称' ,
end_city_id   string COMMENT '末端网点所属城市id' ,
end_city_name   string COMMENT '末端网点所属城市名称' ,
end_area_id     string COMMENT '末端网点所属区县id' ,
end_area_name   string COMMENT '末端网点所属区县名称' ,
end_franchisee_code   string COMMENT '末端网点所属加盟商编码' ,
end_franchisee_name   string COMMENT '末端网点所属加盟商名称' ,
final_sign_network_code   string COMMENT '最终签收派件网点编码' ,
final_sign_network_name   string COMMENT '最终签收派件网点名称' ,
order_source_name      string COMMENT '订单来源' ,
order_source_code      string COMMENT '订单来源code' ,
need_sign_count_1d   string COMMENT '1天应签收' ,
retention_count_1d   string COMMENT '1天内滞留' ,
need_sign_count_2d   string COMMENT '2天应签收' ,
retention_count_2d   string COMMENT '2天内滞留' ,
need_sign_count_3d   string COMMENT '3天应签收' ,
retention_count_3d   string COMMENT '3天内滞留' ,
need_sign_count_5d   string COMMENT '5天应签收' ,
retention_count_5d   string COMMENT '5天内滞留' ,
need_sign_count_7d   string COMMENT '7天应签收' ,
retention_count_7d   string COMMENT '7天内滞留' ,
need_sign_count_10d  string COMMENT '10天应签收' ,
retention_count_10d  string COMMENT '10天内滞留' ,
need_sign_count_15d  string COMMENT '15天应签收' ,
retention_count_15d  string COMMENT '15天内滞留' ,
need_sign_count_20d  string COMMENT '20天应签收' ,
retention_count_20d  string COMMENT '20天内滞留' ,
final_plan_sign_date   date COMMENT '规划签收日期',
third_code string comment'三段码code',
third_name string comment'三段码名字',
need_sign_over_20d int comment '超20天应签收',
retention_over_20d int comment '超20天内滞留'
  )
comment '网点滞留件汇总新'
PARTITIONED BY ( dt string COMMENT '时间分区')
stored as parquet
LOCATION '/dw/hive/jms_dm.db/external/dm_network_retention_summary_dt'
TBLPROPERTIES (     'discover.partitions' = 'false',  'parquet.column.index.access' = 'true'
);

    alter table jms_dm.dm_network_retention_summary_dt add columns (
       retention_count_4d int comment '4天内滞留',
        need_sign_count_4d int comment '4天应签收',
        retention_count_6d int comment '6天内滞留',
        need_sign_count_6d int comment '6天应签收',
        need_sign_over_7d int comment '超7天应签收',
        retention_over_7d int comment '超7天内滞留'
    ) cascade ;